﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace thinger.ADONETTeach
{
    /// <summary>
    /// 基础通用类的测试
    /// </summary>
    class CRUDBaseForHelper
    {
        public void Insert()
        {
            string sql = "insert into Employee(EployName, Gender, NowAddress, IdNo,WeiXinNumber,PhoneNumber, OtherWork, EntryDate, PostId, DepartmentId)";
            sql += "values('Kiter81', '男', '江西', '823456789012345111', '8hinge221', '18370722333', '暂无', '2020-01-03', 14, 14)";
            int result = 0;
            try
            {
                result = SQLHelperBase.ExecuteNonQuery(sql);
            }
            catch(Exception ex)
            {
                Console.WriteLine(ex.Message);
            }
            Console.WriteLine("受影响的行数：" + result);
        }
        public void Update()
        {
            string sql = "update Employee set EployName = '黄老师', Gender='女' where EployeeId=1001";
            int result = SQLHelperBase.ExecuteNonQuery(sql);
            Console.WriteLine("受影响的行数：" + result);
        }
        public void GetSingleResult()
        {
            string sql = "select count(*) as 员工总数 from employee";
            object result = SQLHelperBase.ExecuteScalar(sql);
            Console.WriteLine("员工总数：" +result);
        }

        public void GetReaderList()
        {
            string sql = "select EployName,Gender,NowAddress,IdNo,WeiXinNumber,PhoneNumber from Employee";
            SqlDataReader reader = SQLHelperBase.ExecuteReader(sql);
            //使用循环读取
            while(reader.Read())
            {
                Console.WriteLine($"{reader["EployName"]}\t{reader["Gender"]}\t{reader["NowAddress"]}\t{reader["IdNo"]}");
            }
            reader.Close(); //关闭Reader对象的时候，会自动的关闭对应的连接
        }
        /// <summary>
        /// 返回一个数据集
        /// </summary>
        public void GetDataSet1()
        {
            string sql = "select EployName,Gender,NowAddress,IdNo,WeiXinNumber,PhoneNumber from Employee";
            DataSet ds = SQLHelperBase.GetDataSet(sql, "Employee");
            //遍历DataTable
            foreach (DataRow row in ds.Tables["Employee"].Rows)
            {
                Console.WriteLine($"{row["EployName"]}\t{row["Gender"]}\t{row["NowAddress"]}\t{row["IdNo"]}");
            }
        }
        /// <summary>
        /// 返回一个数据集包含多张数据表
        /// </summary>
        public void GetDataSet2()
        {
            string sql1 = "select EployName,Gender,NowAddress,IdNo,WeiXinNumber,PhoneNumber from Employee";
            string sql2 = "select DepartmentId,DepartmentName from Department";
            string sql3 = "select PostId,PostName from Post";
            Dictionary<string, string> dicTableAndSql = new Dictionary<string, string>
            {
                ["Employee"] = sql1,
                ["Department"] = sql2,
                ["Post"] = sql3
            };
            DataSet ds = SQLHelperBase.GetDataSet(dicTableAndSql);
            //遍历DataTable
            foreach (DataTable dt in ds.Tables)
            {
                Console.WriteLine($"{dt.TableName}\t{dt.Rows.Count}");
            }
            Console.WriteLine("--------------------------------");
            //遍历第一个表的DataTable的数据行
            foreach (DataRow row in ds.Tables["Employee"].Rows)
            {
                Console.WriteLine($"{row["EployName"]}\t{row["Gender"]}\t{row["NowAddress"]}\t{row["IdNo"]}");
            }
            Console.WriteLine("--------------------------------");
            foreach (DataRow row in ds.Tables["Department"].Rows)
            {
                Console.WriteLine($"{row["DepartmentId"]}\t{row["DepartmentName"]}");
            }
            Console.WriteLine("--------------------------------");
            foreach (DataRow row in ds.Tables["Post"].Rows)
            {
                Console.WriteLine($"{row["PostId"]}\t{row["PostName"]}");
            }
        }
    }
}
